﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace VietKinsNutrition.Data
{
    public class ClsTblChitietkienthucnoitroDAL
    {
        ClsBindingBLL ClsBin = new ClsBindingBLL();
        //ClsConnection ClsCnn = new ClsConnection();
        //
        public DataTable TblChitietkienthucnoitro_GetByLoaikienthucnoitroId(int LoaikienthucnoitroId)
        {
            string NamePrc = QueryString.SPROC_TBLCHITIETKIENTHUCNOITRO_GETBYLOAIKIENTHUCNOITROID;
            string NameTable = "TblChitietkienthucnoitro";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@LoaikienthucnoitroId", LoaikienthucnoitroId));

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);

            return Dset.Tables[0];
        }
        //sproc_TblChitietkienthucnoitro_GetByNhomkienthucIdandName
        public DataTable GetByNhomkienthucIdandName(int LoaikienthucnoitroId, string Tieude)
        {
            string NamePrc = QueryString.SPROC_TBLCHITIETKIENTHUCNOITRO_GETBYNHOMKIENTHUCIDANDNAME;
            string NameTable = "TblChitietkienthucnoitro";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@LoaikienthucnoitroId", LoaikienthucnoitroId));
            Cmm.Parameters.Add(new OleDbParameter("@Tieude", Tieude));

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);

            return Dset.Tables[0];
        }

        public DataTable GetByName(string Tieude)
        {
            string NamePrc = QueryString.SPROC_TBLCHITIETKIENTHUCNOITRO_GETBYNHOMKIENTHUCIDANDNAME;
            string NameTable = "TblChitietkienthucnoitro";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Tieude", Tieude));

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);

            return Dset.Tables[0];
        }

        public DataTable SelectAllById(int Id)
        {
            string NamePrc = QueryString.SPROC_TBLCHITIETKIENTHUCNOITRO_GETBYID;
            string NameTable = "TblChitietkienthucnoitro";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Id", Id));

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);

            return Dset.Tables[0];
        }

        public DataTable SelectAll()
        {
            string NamePrc = QueryString.SPROC_TBLCHITIETKIENTHUCNOITRO_GET;
            string NameTable = "TblChitietkienthucnoitro";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);

            return Dset.Tables[0];
        }

        public void Insert(int LoaikienthucnoitroId, string Tieude, string Noidung)
        {
            string NamePrc = QueryString.SPROC_TBLCHITIETKIENTHUCNOITRO_ADD;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@LoaikienthucnoitroId", LoaikienthucnoitroId));
            Cmm.Parameters.Add(new OleDbParameter("@Tieude", Tieude));
            Cmm.Parameters.Add(new OleDbParameter("@Noidung", Noidung));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        //
        public void Delete(int Id)
        {
            string NamePrc = QueryString.SPROC_TBLCHITIETKIENTHUCNOITRO_DELETE;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Id", Id));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
        public void Edit(int Id, string Tieude, string Noidung)
        {
            string NamePrc = QueryString.SPROC_TBLCHITIETKIENTHUCNOITRO_UPDATE;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Tieude", Tieude));
            Cmm.Parameters.Add(new OleDbParameter("@Noidung", Noidung));
            Cmm.Parameters.Add(new OleDbParameter("@Id", Id));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
    }
}
